import pandas as pd
import matplotlib.pyplot as plt


# 数据预导入
def data_read():
    # 导入 csv 数据
    df1 = pd.read_csv('./meal_order_detail1.csv', encoding='gbk')
    df2 = pd.read_csv('./meal_order_detail2.csv', encoding='gbk')
    df3 = pd.read_csv('./meal_order_detail3.csv', encoding='gbk')

    # 导入 excel 数据
    # df1 = pd.read_excel('./restaurant.xlsx', sheet_name='order_detail1')
    # df2 = pd.read_excel('./restaurant.xlsx', sheet_name='order_detail2')
    # df3 = pd.read_excel('./restaurant.xlsx', sheet_name='order_detail3')

    # 数据合并
    df = pd.concat([df1, df2, df3], axis=0)

    # 查看数据集基本信息
    print(df.head())
    print(df.info)
    print(df.describe())

    return df


# 数据预处理
def data_preprocess(df):
    # 删除不需要的字段
    df.drop(['detail_id', 'dishes_id', 'logicprn_name', 'parent_class_name',
             'picture_file', 'itemis_add', 'cost', 'discount_amt', 'discount_reason',
             'kick_back', 'add_inprice', 'add_info', 'bar_code'],
            axis=1, inplace=True)

    # print(df['dish_name'].unique())

    # 数据清理-菜名
    df['dish_name'] = df['dish_name'].str.replace('\n', '').str.replace(' ', '')

    # 分析数据的实际情况，发现每个菜实际的销售额需要经过计算
    df['amount'] = df['price'] * df['count']

    # 将下单时间转换为 datetime 格式
    df['place_order_time'] = pd.to_datetime(df['place_order_time'])
    # 生成 hour 字段，保存订单发生的“小时”
    df['hour'] = df['place_order_time'].map(lambda x: x.hour)
    # 生成 day 字段，保存订单发生的“日期”
    df['day'] = df['place_order_time'].map(lambda x: x.day)
    # 生成 weekday 字段，保存订单发生在“星期几”
    df['weekday'] = df['place_order_time'].map(lambda x: x.weekday() + 1)


# 数据可视化
def data_visualization(df):
    # matplotlib.pyplot 画图中支持中文显示
    plt.rcParams['font.family'] = 'SimHei'

    # 菜出现在订单中的数量前10
    order_count = df['dish_name'].value_counts()[:10]
    # 设置图标题
    plt.title('菜品出现在订单中的数量前10')
    # pandas 支持 Series 数据类型直接画图
    # kind='line': 折线图
    order_count.plot(kind='line', color=['r'])
    # kind='bar': 柱状图
    order_count.plot(kind='bar', fontsize=16)
    # 在图上对应位置显示数字、文字
    for x, y in enumerate(order_count):
        plt.text(x, y + 2, y, ha='center', fontsize=12)
    # 调节画图布局
    plt.subplots_adjust(left=0.1, right=0.9, bottom=0.4, top=0.95)
    # 保存图片
    plt.savefig('./pictures/order_count_top10.png')
    # 关闭 plt
    plt.close()

    # 销售数量最多的菜前10
    dish_count = df.groupby(['dish_name'])['count'].sum().sort_values(ascending=False)[:10]
    plt.title('销售数量最多的菜前10')
    dish_count.plot(kind='line', color=['r'])
    dish_count.plot(kind='bar', fontsize=16)
    for x, y in enumerate(dish_count):
        plt.text(x, y + 2, y, ha='center', fontsize=12)
    plt.subplots_adjust(left=0.1, right=0.9, bottom=0.4, top=0.95)
    plt.savefig('./pictures/dish_count_top10.png')
    plt.close()

    # 销售额最高的菜前10
    dish_amount = df.groupby(['dish_name'])['amount'].sum().sort_values(ascending=False)[:10]
    plt.title('销售额最高的菜前10')
    dish_amount.plot(kind='line', color=['r'])
    dish_amount.plot(kind='bar', fontsize=16)
    plt.title('销售额最高的菜前10')
    for x, y in enumerate(dish_amount):
        plt.text(x, y + 2, y, ha='center', fontsize=12)
    plt.subplots_adjust(left=0.1, right=0.9, bottom=0.4, top=0.95)
    plt.savefig('./pictures/dish_amount_top10.png')
    plt.close()

    # 消费金额最高订单前10
    order_amount = df.groupby(['order_id'])['amount'].sum().sort_values(ascending=False)[:10]
    plt.title('消费金额最高订单前10')
    order_amount.plot(kind='line', color=['r'])
    order_amount.plot(kind='bar', fontsize=16)
    for x, y in enumerate(order_amount):
        plt.text(x, y + 2, y, ha='center', fontsize=12)
    plt.subplots_adjust(left=0.1, right=0.9, bottom=0.15, top=0.95)
    plt.savefig('./pictures/order_amount_top10.png')
    plt.close()

    # 销冠-订单数前10
    waiter_order = df.groupby(['emp_id'])['order_id'].count().sort_values(ascending=False)[:20]
    plt.title('销冠-订单数前10')
    waiter_order.plot(kind='line', color=['r'])
    waiter_order.plot(kind='bar', fontsize=16)
    for x, y in enumerate(waiter_order):
        plt.text(x, y + 2, y, ha='center', fontsize=12)
    plt.subplots_adjust(left=0.1, right=0.9, bottom=0.15, top=0.95)
    plt.savefig('./pictures/waiter_order_top10.png')
    plt.close()

    # 销冠-营业额前10
    waiter_amount = df.groupby(['emp_id'])['amount'].sum().sort_values(ascending=False)[:10]
    plt.title('销冠-营业额前10')
    waiter_amount.plot(kind='line', color=['r'])
    waiter_amount.plot(kind='bar', fontsize=16)
    for x, y in enumerate(waiter_amount):
        plt.text(x, y + 2, y, ha='center', fontsize=12)
    plt.subplots_adjust(left=0.1, right=0.9, bottom=0.15, top=0.95)
    plt.savefig('./pictures/waiter_amount_top10.png')
    plt.close()

    # 按小时统计销售量
    hour_count = df.groupby(['hour'])['count'].sum()
    plt.title('按小时统计销售量')
    hour_count.plot(kind='bar', fontsize=16)
    plt.savefig('./pictures/hour_count.png')
    plt.close()

    # 按小时统计销售额
    hour_amount = df.groupby(['hour'])['amount'].sum()
    plt.title('按小时统计销售额')
    hour_amount.plot(kind='bar',fontsize=16)
    plt.savefig('./pictures/hour_amount.png')
    plt.close()

    # 按天统计销售量
    day_count = df.groupby(['day'])['count'].sum()
    plt.title('按天统计销售量')
    day_count.plot(kind='bar', fontsize=16)
    plt.savefig('./pictures/day_count.png')
    plt.close()

    # 按天统计销售额
    day_amount = df.groupby(['day'])['amount'].sum()
    plt.title('按天统计销售额')
    day_amount.plot(kind='bar',fontsize=16)
    plt.savefig('./pictures/day_amount.png')
    plt.close()

    # 按星期几统计销售量
    weekday_count = df.groupby(['weekday'])['count'].sum()
    plt.title('按星期几统计销售量')
    weekday_count.plot(kind='bar', fontsize=16)
    plt.savefig('./pictures/weekday_count.png')
    plt.close()

    # 按星期几统计销售额
    weekday_amount = df.groupby(['weekday'])['amount'].sum()
    plt.title('按星期几统计销售额')
    weekday_amount.plot(kind='bar',fontsize=16)
    plt.savefig('./pictures/weekday_amount.png')
    plt.close()

    for emp_id in waiter_order.index:
        hour_count = df[df['emp_id'] == emp_id].groupby(['hour'])['count'].sum()
        plt.title(f'员工{emp_id}按小时统计销售量')
        hour_count.plot(kind='bar', fontsize=16)
        plt.savefig(f'./pictures/{emp_id}_hour_count.png')
        plt.close()


# 数据挖掘
def data_mining(df):
    # 菜名-记录数
    dish_name_count = df['dish_name'].value_counts()
    # 订单-点菜种类数量
    order_id_count = df['order_id'].value_counts()
    # 订单总数
    n = len(order_id_count)

    # 每种菜占订单的比例（也就是：多少比例的订单有这种菜）
    dish_name_order_id_ratio = {}
    for dish_name in dish_name_count.index:
        dish_name_order_id_ratio[dish_name] = dish_name_count[dish_name] / n

    # 建立 订单号-菜名 数据结构（这里选用 dict）
    # 准备一个字典作为中间数据
    order_id_dish_name_dict = dict()
    # 使用 groupby 函数获得 订单号-菜名 的 Series
    order_id_dish_name = df.groupby(['order_id'])['dish_name']
    # 遍历 order_id_dish_name 中所有的 order_id
    for order_id in order_id_dish_name.indices:
        # 如果 order_id 不存在于 order_id_dish_name_dict 的主键 key 中，
        # 那么就对 order_id_dish_name_dict[order_id] 进行初始化，赋值为一个空的集合 set()
        if order_id not in order_id_dish_name_dict:
            order_id_dish_name_dict[order_id] = set()
        # 将 order_id_dish_name 中对应的订单号下的 菜名（dishes_name）添加到 order_id_dish_name_dict 对应的主键中
        for obj_id in order_id_dish_name.indices[order_id]:
            order_id_dish_name_dict[order_id].add(df['dish_name'].array[obj_id])

    # 根据概率相关性，检查任意（2种）菜名组合存在于同一订单的情况
    # 设置阈值
    threshold = 10
    # 2层 for 循环，遍历2种菜的所有组合（组合！！！）
    for i, dish_name1 in enumerate(dish_name_count.index):
        for j, dish_name2 in enumerate(dish_name_count.index):
            # i < j 确保相同的组合不重复计算，且不会有2个相同的菜名形成组合
            if i < j:
                # 菜i的在订单中出现的概率
                P_Ai = dish_name_order_id_ratio[dish_name1]
                # 菜j的在订单中出现的概率
                P_Aj = dish_name_order_id_ratio[dish_name2]
                # P(Ai) * P(Aj)
                P_Ai_P_Aj = P_Ai * P_Aj
                # P(Ai, Aj)
                m = 0
                for order_id in order_id_dish_name_dict:
                    if {dish_name1, dish_name2}.issubset(order_id_dish_name_dict[order_id]):
                        m += 1
                P_Ai_Aj = m / n

                # 根据阈值，查找需要的相关（关联）关系
                if P_Ai_P_Aj > 0 and P_Ai_Aj / P_Ai_P_Aj >= threshold:
                    print(f"'{dish_name1}'、'{dish_name2}'同时点的概率：P(Ai, Aj) = {round(P_Ai_Aj, 4)}，大于"
                          f"P(Ai) * P(Aj) = {round(P_Ai_P_Aj, 4)} {threshold}倍")


if __name__ == "__main__":
    df = data_read()
    data_preprocess(df)
    data_visualization(df)
    data_mining(df)